/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models;


import controllers.entity.Children;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author hoangnv
 */
public class ChildrenDAO {
    private static PreparedStatement prst;
    
    public List <Children>  getAllChildrenByMaCanBo(int MaCanBo){
        List <Children> list = new ArrayList<>();
        boolean ok = DBConnect.Connect();
        if (!ok) {
            DBConnect.Close();
            return null;
        }
        try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("SELECT * FROM ConCai WHERE MaCanBo = ?");
            prst.setInt(1, MaCanBo);
            ResultSet rs = prst.executeQuery();
            while (rs.next()) {
                int maCanBo = rs.getInt("MaCanBo");
                String maConCai = rs.getString("MaConCai");
                String hoTen = rs.getString("HoVaTen");
                Date  ngaySinh = rs.getDate("NgaySinh");
                String thanhTich = rs.getString("ThanhTich");
                String gioitinh=rs.getString("GioiTinh");       
                Children temp = new Children(maCanBo,maConCai,hoTen,ngaySinh,thanhTich,gioitinh);
                
                list.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        DBConnect.Close();
        return list;
    }
    public static List <Children>  getAllChildren(){
        List <Children> list = new ArrayList<>();
        boolean ok = DBConnect.Connect();
        if (!ok) {
            DBConnect.Close();
            return null;
        }
        try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("SELECT * FROM ConCai");
            ResultSet rs = prst.executeQuery();
            while (rs.next()) {
                int maCanBo = rs.getInt("MaCanBo");
                String maConCai = rs.getString("MaConCai");
                String hoTen = rs.getString("HoVaTen");
                Date  ngaySinh = rs.getDate("NgaySinh");
                String thanhTich = rs.getString("ThanhTich");
                String gioitinh=rs.getString("GioiTinh");       
                Children temp = new Children(maCanBo,maConCai,hoTen,ngaySinh,thanhTich,gioitinh);
                
                list.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        DBConnect.Close();
        return list;
    }
    public int getCountCon(){
        int count = 0;
        List <Children> list = getAllChildren();
        for (Children ch : list){
            Integer i = Integer.parseInt(ch.getMaConCai());
            System.out.println(i);
            if (i > count ) count = i;
        }
        return count;
    }
     public static List <Children>  getAllChildrenHSG(){
        List <Children> list = new ArrayList<>();
        boolean ok = DBConnect.Connect();
        if (!ok) {
            DBConnect.Close();
            return null;
        }
        try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("SELECT * FROM ConCai WHERE thanhtich LIKE?");
            prst.setString(1, "%" + "HSG" + "%");
            ResultSet rs = prst.executeQuery();
            while (rs.next()) {
                int maCanBo = rs.getInt("MaCanBo");
                String maConCai = rs.getString("MaConCai");
                String hoTen = rs.getString("HoVaTen");
                Date  ngaySinh = rs.getDate("NgaySinh");
                String thanhTich = rs.getString("ThanhTich");
                String gioitinh=rs.getString("GioiTinh");       
                Children temp = new Children(maCanBo,maConCai,hoTen,ngaySinh,thanhTich,gioitinh);
                
                list.add(temp);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        DBConnect.Close();
        return list;
    }
    public boolean InsertChildren(Children X){
        boolean ok = DBConnect.Connect();
        if (!ok) {
            DBConnect.Close();
            return false;
        }
       try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("INSERT INTO ConCai"
                    + "(MaCanBo,MaConCai,HoVaTen,NgaySinh,ThanhTich,GioiTinh)"
                    + "VALUES (?,?,?,?,?,?)");
            prst.setInt(1,X.getMaCanBo());
            prst.setString(2, X.getMaConCai());
            prst.setString(3,X.getHoTen());
            prst.setDate(4,new java.sql.Date((X.getNgaySinh()).getTime()));
            prst.setString(5,X.getThanhTich());
            prst.setString(6,X.getGoiTinh());
            if (prst.executeUpdate() >0) {
                DBConnect.Close();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
       DBConnect.Close();
       return false;
    }
    public boolean UpDateChildren(Children X){
        boolean ok = DBConnect.Connect();
        if (!ok) {
            DBConnect.Close();
            return false;
        }
       try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("UPDATE ConCai SET HoVaTen = ?,NgaySinh = ?,GioiTinh=?,ThanhTich = ?,MaCanBo=? WHERE MaConCai = ?");
            prst.setString(1,X.getHoTen());
            prst.setDate(2,new java.sql.Date((X.getNgaySinh()).getTime()));
            prst.setString(3,X.getGoiTinh());
            prst.setString(4,X.getThanhTich());
            prst.setInt(5,X.getMaCanBo());
            prst.setString(6,X.getMaConCai());
            System.out.println("%"+X.getMaConCai()+"%");
            if (prst.executeUpdate() >0) {
                DBConnect.Close();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
       DBConnect.Close();
       return false;
    }
    public boolean DeleteChildrenByMaConCai(String MaConCai){
       boolean ok = DBConnect.Connect();
       if (!ok) {
            DBConnect.Close();
            return false;
       }
       try {
            prst = (PreparedStatement) DBConnect.con.prepareStatement("DELETE FROM ConCai WHERE MaConCai = ? ");
            prst.setString(1,MaConCai);
            if (prst.executeUpdate() >0) {
                DBConnect.Close();
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
       DBConnect.Close();
       return false;
    }
}
    

